<?php

require __DIR__ . '/../bootstrap.php';
require_once __DIR__ . "/../auth.php";
require_once __DIR__ . "/functions.php";
$em = require __DIR__ . '/../example/em.php';

$auth = new \Auth\Auth($em);
$user = $auth->getUser();
//BEGIN KERCH ///////////////////////////////////////////////////////
if (isset($_GET['dateBegin'])) {
    $dateBegin = convertDate($_GET['dateBegin']);
}

if (isset($_GET['dateEnd'])) {
    $dateEnd = convertDate($_GET['dateEnd']);
}

$sql = "SELECT biling.`date`
     , user.name
     , sim.number
     , biling.iccid
     , biling.type
     , biling.other
     , biling.duration
     , biling.amount
     , biling.paysystem
FROM
  biling
INNER JOIN sim
ON biling.phone = sim.id
INNER JOIN sim_owner
ON sim_owner.sim_id = sim.id
INNER JOIN user
ON sim_owner.user_id = user.id
WHERE
  biling.`date` BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
  AND sim_owner.state = 1
  AND user.lft >= {$user->getLeft()}
  AND user.rgt <= {$user->getRight()}
ORDER BY
  biling . `date`";


$rows = $em->getConnection()->query($sql)->fetchAll();

//echo '<pre>';
//print_r($rows);
//echo '</pre>';

$result["aaData"] = array();
$i = 0;
foreach ($rows as $row) {
    switch ($row['type']) {
        case 'FILLUP' :
            $row['type'] = '<i class="splashy-add"></i> Пополнение на сумму <strong>' . $row['amount'] . '</strong>';
            $row['other'] = 'через <strong>' . $row['paysystem'] . '</strong>';
            break;
        case 'ORIG' :
            $seconds = $row['duration'];
            $h = floor($seconds / 3600);
            $m = floor(($seconds % 3600) / 60);
            $s = ($seconds % 3600) % 60;
            $row['type'] = '<i class="splashy-arrow_large_left"></i> Исходящий вызов: <strong>' . $h . " ч . " . $m . " мин . " . $s . " сек .</strong > ";
            $row['other'] = 'на номер <strong>' . $row['other'] . '</strong>';
            break;
        case 'TERM' :
            $seconds = $row['duration'];
            $h = floor($seconds / 3600);
            $m = floor(($seconds % 3600) / 60);
            $s = ($seconds % 3600) % 60;
            $row['type'] = '<i class="splashy-arrow_large_right_outline"></i> Входящий вызов: <strong>' . $h . " ч . " . $m . " мин . " . $s . " сек .</strong > ";
            if ($row['other'] == 0) {
                $row['other'] = '<strong>Номер скрыт</strong>';
            } else {
                $row['other'] = 'с номера <strong>' . $row['other'] . '</strong>';
            }
            break;
        default:
            break;
    }
    $i++;
    $result["aaData"][] = array(
        $i,
        $row['date'],
        $row['name'],
        $row['number'],
        $row['iccid'],
        $row['type'],
        $row['other']);
}
echo json_encode($result);

exit();
//END KERCH ///////////////////////////////////////////////////////


$dateBegin = '2011-01-01';
if (isset($_GET['dateBegin'])) {
    $dateBegin = convertDate($_GET['dateBegin']);
}

$dateEnd = date("Y - m - d");
if (isset($_GET['dateEnd'])) {
    $dateEnd = convertDate($_GET['dateEnd']);
}

$getTime = function ($seconds) {
    $datetime = new DateTime();
    $datetime->setTime(0, 0, $seconds);
    return $datetime->format('H:i:s');
};

$getLastPaysystem = function () use ($em, $dateBegin, $dateEnd) {

    $sql = "
    select phone, paysystem 
    from biling
    where id in(
    select max(id)
        from biling
        where date_record between '$dateBegin 00:00:00' and '$dateEnd 23:59:59'
        group by phone
    );";

    $rows = $em->getConnection()->query($sql)->fetchAll();
    $result = array();
    foreach ($rows as $row) {
        $result[$row['phone']] = $row['paysystem'];
    }

    return $result;

};

$sql = "
select biling . phone,
        `user` . `name`,
        biling . iccid,
        sim . `number`,
        sum(biling . amount) as amount,
        sum(if (biling . `type` = 'ORIG', 1, 0)) as out_call,
        sum(if (biling . `type` = 'TERM', 1, 0)) as in_call,
        sum(if (biling . `type` = 'ORIG', biling . duration, 0)) as out_min,
        sum(if (biling . `type` = 'TERM', biling . duration, 0)) as in_min
from `user`
        inner join sim_owner on `user` . id = sim_owner . user_id
        inner join biling on sim_owner . sim_id = biling . phone
        inner join sim on sim . id = biling . phone
where `user` . lft >= {
    $user->getLeft()}
        and `user` . rgt <= {
    $user->getRight()}
        and sim_owner . `state` = 1
and biling . date_record between '$dateBegin 00:00:00' and '$dateEnd 23:59:59'
group by 
        `user` . `name`,
        biling . iccid,
        sim . `number`

";
//print_r($sql);
//exit();

$rows = $em->getConnection()->query($sql)->fetchAll();
/*
echo '<pre>';
print_r($rows);
echo '<pre>';
exit();
*/
$lastPaysystem = $getLastPaysystem();

$result["aaData"] = array();
$i = 0;
foreach ($rows as $row) {
    $i++;
    $result["aaData"][] = array(
        $i,
        $row['name'],
        '',
        '',
        $row['amount'],
        isset($lastPaysystem[$row['phone']]) ? $lastPaysystem[$row['phone']] : '', // тип пополнения
        $row['number'],
        $row['iccid'],
        $row['out_call'],
        $row['in_call'],
        $getTime($row['out_min']),
        $getTime($row['in_min']),
        '');
}
echo json_encode($result);